package com.gxa.day2022_08_09_28.emp_dept_system.dao.impl;



import com.gxa.day2022_08_09_28.emp_dept_system.dao.EmpDao;
import com.gxa.day2022_08_09_28.emp_dept_system.entity.Dept;
import com.gxa.day2022_08_09_28.emp_dept_system.entity.Emp;
import com.gxa.day2022_08_09_28.emp_dept_system.utils.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {
    @Override
    public List<Emp> queryAll() {
        List<Emp> emps = new ArrayList<>();

        Connection connection = DBConnection.getConnection();
        //两表一起查
        String sql = "SELECT e.empno,e.ename,e.job,e.HIREDATE,e.SAL,e.COMM,e.mgr,d.deptno,d.dname ,d.loc " +
                "FROM emp e,dept d " +
                "WHERE e.deptno=d.deptno";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);

//            rs = ps.executeQuery();
            //execute 增加 删除 修改 查询    返回的布尔值不是是否执行成功 而是 是否有结果结果集!!
            //executeUpdate 增加 删除 修改  返回的整形 是指增删改操作影响的记录条数
            //executeQuery 查询           返回的是结果集
            boolean flag = ps.execute();
            if(flag){
                rs = ps.getResultSet();
                while(rs.next()) {
                    int empno = rs.getInt("empno");
                    String ename = rs.getString("ename");
                    String job = rs.getString("job");
                    Integer mgr = rs.getInt("mgr");
                    Date hiredate = rs.getDate("hiredate");
                    double sal = rs.getDouble("sal");
                    Double comm = rs.getDouble("comm");

                    Integer deptno = rs.getInt("deptno");
                    String dname = rs.getString("dname");
                    String loc = rs.getString("loc");

                    Dept dept = new Dept(deptno,dname,loc);

                    Emp emp = new Emp(empno,ename,job,mgr,hiredate,sal,comm);
                    emp.setDept(dept);

                    emps.add(emp);

                }
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnection.close(rs,ps,connection);
        }

        return emps;
    }

    @Override
    public boolean save(Emp emp) {


        Connection connection = DBConnection.getConnection();
        String sql = "INSERT INTO emp(empno,ename,job,deptno) VALUES(?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,emp.getEmpno());
            ps.setString(2,emp.getEname());
            ps.setString(3,emp.getJob());
            ps.setInt(4,emp.getDept().getDeptno());

//            boolean flag = ps.execute();//boolean 表示是否有结果集
            int rows = ps.executeUpdate();//返回类型是影响的行数
            return rows ==1;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnection.close(ps,connection);
        }
        return false;
    }

    @Override
    public Emp getById(Integer id) {

        Connection connection = DBConnection.getConnection();
        String sql = "SELECT e.empno,e.ename,e.job,e.HIREDATE,e.SAL,e.COMM,e.mgr,d.deptno,d.dname ,d.loc " +
                "FROM emp e,dept d " +
                "WHERE e.deptno=d.deptno AND e.empno=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);


            rs = ps.executeQuery();
            if(rs.next()) {

                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                Date hiredate = rs.getDate("hiredate");
                double sal = rs.getDouble("sal");
                Double comm = rs.getDouble("comm");



                Integer deptno = rs.getInt("deptno");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");

                Dept dept = new Dept(deptno,dname,loc);

                Emp emp = new Emp(empno,ename,job,mgr,hiredate,sal,comm);
                emp.setDept(dept);

                return emp;
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnection.close(rs,ps,connection);
        }

        return null;
    }

    @Override
    public void update(Emp emp) {
        Connection connection = DBConnection.getConnection();
        String sql = "UPDATE emp SET ename=?,job=?,deptno=? WHERE empno=?";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,emp.getEname());
            ps.setString(2,emp.getJob());
            ps.setInt(4,emp.getEmpno());
            ps.setInt(3,emp.getDept().getDeptno());

            ps.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnection.close(ps,connection);
        }
    }

    @Override
    public void delete(Integer id) {
        Connection connection = DBConnection.getConnection();
        String sql = "DELETE FROM emp WHERE empno=?";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);

            ps.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnection.close(ps,connection);
        }
    }
}
